package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.NoticeListDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/3/3.
 */
public class NoticeListRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<NoticeListDto> getNoticeList4DT(Integer start, Integer length, Integer userId, String search) throws Exception {

        String sql = "select tu.UserName as createUserName,tun.userNoticeId ,tn.NoticeTitle,tn.NoticeContent," +
                "tn.NoticeSendTime,tun.isRead from tb_notice tn,tb_user tu,tb_user_notice tun\n" +
                "where tun.userId=:userId and tn.NoticeId=tun.NoticeId and tun.userId=tu.userId";
        if (search != "" || search != null) {
            sql += " AND tn.NoticeTitle LIKE '%" + search + "%'";
        }
        sql += " ORDER BY tun.isRead , tn.noticeSendTime DESC";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(NoticeListDto.class));
        query.setParameter("userId", userId);
        List<NoticeListDto> dtos = query.setFirstResult(start).setMaxResults(length).getResultList();
        return dtos;
    }

    public Integer getNoticeListCount4DT(String search, Integer userId) throws Exception {
        String sql = "SELECT COUNT(*)\n" +
                " FROM tb_notice n JOIN tb_user_notice un ON un.NoticeId=n.NoticeId\n" +
                " WHERE n.NoticeType=0 AND un.UserId=:userId\n";
        if (!search.equals("")) {
            sql += " AND n.NoticeTitle LIKE '%" + search + "%'";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class);
        query.setParameter("userId", userId);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<NoticeListDto> getNoticeByUserId4APP(Integer userId, Integer mCount, Integer mySize) throws Exception {
        String sql = "select un.userNoticeId, un.isRead, un.readTime, un.noticeId, n.noticeTitle, " +
                "n.noticeContent, n.noticeSendTime " +
                "from tb_user_notice un, tb_notice n " +
                "where un.noticeId = n.noticeId and un.userId = :userId and n.noticeType = 0 " +
                "order by un.isRead , n.noticeSendTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(NoticeListDto.class));
        query.setParameter("userId", userId);
        return query.setFirstResult((mCount - 1) * mySize).setMaxResults(mySize).getResultList();
    }

}
